package com.kwum.excel;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class MySQLUtils {

    public static void main(String[] args) throws Exception{
        
        //获得数据库的连接
        Connection conn = getMySQLConnection("topbuzz_release");
        //通过数据库的连接操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select username, password from account");//选择import java.sql.ResultSet;
        while(rs.next()){//如果对象中有数据，就会循环打印出来
            System.out.println(rs.getString("username")+","+rs.getString("password"));
        }
        
    }
    
    public static Connection getMySQLConnection(String databaseName) throws Exception{
        //声明Connection对象
        Connection con;
        //驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        //URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://localhost:3306/" + databaseName + "?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";
        //MySQL配置时的用户名
        String user = "root";
        //MySQL配置时的密码
        String password = "Kwum123";
        //加载驱动程序
        Class.forName(driver);
        //1.getConnection()方法，连接MySQL数据库！！
        con = DriverManager.getConnection(url,user,password);
        if(!con.isClosed()){
            System.out.println("The database connection is complete!");
        }
        return con;
    }
    
    public static void insertAccount(String username, String password, String code, Integer typeId) throws Exception{
        //获得数据库的连接
        Connection conn = getMySQLConnection("topbuzz_release");
        
        String sql="insert into account (username, password, code, type_id) values(?, ?, ?, ?)";
        //预编译sql语句
        PreparedStatement psmt = conn.prepareStatement(sql);
        //先对应SQL语句，给SQL语句传递参数
        psmt.setString(1, username);
        psmt.setString(2, password);
        psmt.setString(3, code);
        psmt.setInt(4, typeId);
        //执行SQL语句
        psmt.execute();
        conn.close();
    }
}
